/****** Object:  StoredProcedure [dbo].[sp_SearchUnfinishedProducts]    Script Date: 05/22/2011 16:17:53 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_SearchUnfinishedProducts]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_SearchUnfinishedProducts]
GO

/****** Object:  StoredProcedure [dbo].[sp_SearchUnfinishedProducts]    Script Date: 05/22/2011 16:17:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_SearchUnfinishedProducts]

	@Size			NVARCHAR(30),
	@Material		NVARCHAR(30),
	@Description	NVARCHAR(100)
	
AS
BEGIN
	
	SET NOCOUNT ON;
	
	SET @Size = LTRIM(RTRIM(@Size))
	SET @Material = LTRIM(RTRIM(@Material))
	SET @Description = LTRIM(RTRIM(@Description))
	
	IF(LOWER(@Size) = 'All')
		SET @Size = '%'
	IF(LOWER(@Material) = 'All')
		SET @Material = '%'
	IF(LEN(@Description) = 0)
		SET @Description = '%';	

	WITH [TAB1] AS
	(
		/*********************************************************
		
		SELECT [So don hang], [Ma khach hang], [Ten khach hang]
		
		**********************************************************/
		SELECT [Order].[id], [Order].[customerId], [Order].[code], [Customer].[name]
		FROM [Order], [Customer]
		WHERE [isFinished] = 0
			  AND [Order].[customerId] = [Customer].[id]
	), [TAB2] AS
	(
		/************************************************************************
		
		SELECT TAB1 + [Ma nguyen lieu], [Quy cach], [So luong], [Hoan thanh]
		
		*************************************************************************/
		SELECT TAB1.[code], TAB1.[customerId], TAB1.[name], [OrderMaterial].[materialId], [OrderMaterial].[description],
			   [OrderMaterial].[orderQuantity], [OrderMaterial].[finishedQuantity], ([Size].[length] + ' x ' + [Size].[width]) AS [Size], [orderId]
		FROM TAB1, [OrderMaterial], [Size]
		WHERE [TAB1].[id] = [OrderMaterial].[orderId]
			  AND [Size].[isActive] = 1	
			  AND [finishedQuantity] > 0		
	)
	SELECT *
	FROM TAB2
	WHERE   [Size] LIKE @Size AND
			[materialId] LIKE @Material AND
			[description] LIKE @Description			
END


GO


